package DBLayer;

import Model.Supplier;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

/**
 *
 * @author Administrator
 */
public class DbSupplier implements DbInterfaceSupplier {
	private Connection db_con;

	public DbSupplier() {
		db_con = DbConnection.getInstance().getDBcon();
	}

	public ArrayList<Supplier> getAllSuppliers(String wClause) {
		return miscWhere(wClause);
	}

	public Supplier searchById(String id) {
		String wClause = "id = '"+id+"'";
		return singleWhere(wClause);
	}

	public Supplier searchByPhone(String phone) {
		String wClause = "phone = '"+phone+"'";
		return singleWhere(wClause);
	}

	public Supplier searchByEmail(String email) {
		String wClause = "email = '"+email+"'";
		return singleWhere(wClause);
	}

	public int insertSupplier(Supplier sup) {
		int result = -1;
		String query = "INSERT INTO suppliers(name, address, zip, city, country, phone, email) VALUES"
				+ "('" + sup.getName() + "', '" + sup.getAddress() + "', '" + sup.getZip() + "', '"
				+ sup.getCity() + "', '" + sup.getCountry() + "', '" + sup.getPhone() + "', '" + sup.getEmail() + "')";
		System.out.println("insert: " + query);
		try {
			Statement stmt = db_con.createStatement();
			stmt.setQueryTimeout(5);
			result = stmt.executeUpdate(query);
			stmt.close();
		} catch (Exception ex) {
			System.out.println("Insert exception in supplier db: " + ex);
		}
		return result;
	}

	public int updateSupplier(Supplier sup) {
		int result = -1;

		String query="UPDATE suppliers SET ";
		if (sup.getName() != null) query += "name = '"+sup.getName()+"',";
		if (sup.getAddress() != null) query += "address = '"+sup.getAddress()+"',";
		if (sup.getZip() != null) query += "zip = '"+sup.getZip()+"',";
		if (sup.getCity() != null) query += "city = '"+sup.getCity()+"',";
		if (sup.getCountry() != null) query += "country = '"+sup.getCountry()+"',";
		if (sup.getPhone() != null) query += "phone = '"+sup.getPhone()+"',";
		if (sup.getEmail() != null) query += "email = '"+sup.getEmail()+"',";
		if (query.endsWith(",")) query = query.substring(0, query.length()-1);
		query += " WHERE id = "+ sup.getId();
                System.out.println("Update query:" + query);
  		try{ // update employee
	 		Statement stmt = db_con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	result = stmt.executeUpdate(query);

	 	 	stmt.close();
		} catch(Exception ex) {
	 	 	System.out.println("Update exception in supplier db: "+ex);
	  	}
		return result;
	}

	public int deleteSupplier(String phone) {
		int result = -1;

	  	String query = "DELETE FROM suppliers WHERE phone = '" +
				phone + "'";
                System.out.println("delete: " + query);
	  	try{ // delete from employees
	 		Statement stmt = db_con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	result = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}//slut try
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in employee db: "+ex);
   	        }
		return result;
	}

	private ArrayList<Supplier> miscWhere(String wClause)
	{
        ResultSet results;
	    ArrayList<Supplier> list = new ArrayList<Supplier>();

	    String query =  buildQuery(wClause);

        try{ // read the employee from the database
			Statement stmt = db_con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			int snr=0;
			while( results.next() ){
				Supplier supObj = new Supplier();
				supObj = buildSupplier(results);
		        list.add(supObj);
			}//end while
			stmt.close();
		} catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return (list.size() > 0 ? list : null);
	}

	//Singelwhere is used when we only select one employee
	private Supplier singleWhere(String wClause)
	{
		ResultSet results;
		Supplier supObj = null;
	    String query =  buildQuery(wClause);
        System.out.println(query);
		try{ // read the employee from the database
	 		Statement stmt = db_con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);

	 		if( results.next() ){
				supObj = buildSupplier(results);
				stmt.close();
			}
		} catch(Exception e) {
	 		System.out.println("Query exception: " + e);
	 	}
		return supObj;
	}

	//method to build the query
	private String buildQuery(String wClause)
	{
	    String query="SELECT * FROM suppliers";

		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;

		return query;
	}

		//method to build an employee object
	private Supplier buildSupplier(ResultSet results)
	{
		Supplier supObj = new Supplier();
        try {
			supObj.setId(Integer.parseInt(results.getString(1)));
            supObj.setName(results.getString(2));
            supObj.setAddress(results.getString(3));
            supObj.setZip(results.getString(4));
			supObj.setCity(results.getString(5));
			supObj.setCountry(results.getString(6));
			supObj.setPhone(results.getString(7));
			supObj.setEmail(results.getString(8));
		} catch(Exception e) {
			System.out.println("error in building the supplier object");
        }
		return supObj;
	}
}
